Skip Headers

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2) Part Number B14192-03 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 6.3 Preparing and Planning Database Restore and Recovery

While RMAN makes carrying out most database restore and recovery tasks much simpler, you still have to plan your database restore and recovery actions based on which database files have been lost and your recovery goal.

RMAN can make most of the important decisions about the restore process for you, but you may want to preview and even override its decisions in some circumstances. For example, if you know a given backup is unavailable, due to a tape being stored offsite or a device being inaccessible, you can direct RMAN to not use that backup during the restore process.

RMAN provides tools to let you preview which backups will be used in a restore, and to validate the contents of the backups to ensure that they can be used in future restore operations.

## 6.3.1 Database Restore and Recovery Procedure: Outline

The basic procedure for performing restore and recovery with RMAN is as follows:

1. Determine which database files must be restored from backup, and which backups (which specific tapes, or specific backup sets or image copies on disk) to use for the restore operation. The files to be restored may include the control file, SPFILE, archived redo log files, and datafiles.

2. Place the database in the state appropriate for the type of recovery that you are performing. For example, if you are recovering a single tablespace or datafile, then you can keep the database open and take the tablespace or datafile offline. If you are restoring all datafiles, then you must shut down the database and then mount it before you can perform the restore.

3. Restore lost database files from backup with the `RESTORE` command. You may restore files to their original locations, or you may have to restore them to other locations if, for instace, a disk has failed. You may also have to update the SPFILE if you have changed the control file locations, or the control file if you have changed the locations of datafiles or redo logs.

4. Perform media recovery on restored datafiles, if any, with the `RECOVER` command.

5. Perform any final steps required to make the database available for users again. For example, re-open the database if necessary, as happens when restoring lost control files, or bring offline tablespaces online if restoring and recovering individual tablespaces.

This outline is intended to encompass a wide range of different scenarios. Depending upon your situation, some of the steps described may not apply. (For example, you do not need to perform media recovery if the only file restored from backup is the SPFILE.) You will have to devise your final recovery plan based on your situation.

## 6.3.2 Determining Which Database Files to Restore or Recover

The methods of determining which files require restore or recovery depend upon the type of file that is lost. This section contains the following topics:

### 6.3.2.1 Recognizing a Lost Control File

It is generally obvious when the control file of your database must be restored. The database shuts down immediately if any of the control file copies becomes inaccessible and reports an error if you try to start it without a valid control file at each location specified in the `CONTROL_FILES` initialization parameter.

Loss of some but not all copies of your control file does not require recovery of the control file from backup. When one copy of the control file is lost, the database will automatically shut down. You can either copy an intact copy of the control file over the damaged or missing control file, or update the parameter file so that does not refer to the damaged or missing control file. Once the `CONTROL_FILES` parameter references only present, intact copies of the control file, you can restart your database.

Note that if you restore the control file from backup, you must perform media recovery of the whole database and then perform an `OPEN RESETLOGS`, even if no datafiles have to be restored.

### 6.3.2.2 Identifying Datafiles Requiring Media Recovery

When and how to recover depends on the state of the database and the location of its datafiles. To determine which if any files require media recovery, use the following procedure:

1. Start SQL*Plus and connect to the target database. For example, issue the following to connect to `trgt`:

       % sqlplus 'SYS/oracle@trgt AS SYSDBA'

2. Determine the status of the database by executing the following SQL query:

       SELECT STATUS FROM V$INSTANCE;

   If the status is `OPEN`, then the database is open. However, some datafiles may require media recovery.

3. Query the `V$DATAFILE_HEADER` view to determine the status of your datafiles. Run the following SQL statements to check the datafile headers:

       COL FILE# FORMAT 999
       COL STATUS FORMAT A7
       COL ERROR FORMAT A10
       COL TABLESPACE_NAME FORMAT A10
       COL NAME FORMAT A30

       SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME
       FROM V$DATAFILE_HEADER
       WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

   Each row returned represents a datafile that either requires media recovery or has an error requiring a restore. Check the `RECOVER` and `ERROR` columns. `RECOVER` indicates whether a file needs media recovery, and `ERROR` indicates whether there was an error reading and validating the datafile header.

   If `ERROR` is not `NULL`, then the datafile header cannot be read and validated. Check for a temporary hardware or operating system problem causing the error. If there is no such problem, you must restore the file or switch to a copy.

   If the `ERROR` column is `NULL` and the `RECOVER` column is `YES`, then the file requires media recovery (and may also require a restore from backup).

   Note: Because `V$DATAFILE_HEADER` only reads the header block of each datafile, it does not detect all problems that require the datafile to be restored. For example, you cannot tell whether a datafile contains corrupt data blocks using `V$DATAFILE_HEADER`.

   You can also query`V$RECOVER_FILE` to list datafiles requiring recovery by datafile number with their status and error information.

       SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME
              FROM V$RECOVER_FILE;

   Note: You cannot use `V$RECOVER_FILE` with a control file restored from backup or a control file that was re-created after the time of the media failure affecting the datafiles. A restored or re-created control file does not contain the information needed to update `V$RECOVER_FILE` accurately.

   To find datafile and tablespace names, you can also perform useful joins using the datafile number and the `V$DATAFILE` and `V$TABLESPACE` views. For example:

       COL DF# FORMAT 999
       COL DF_NAME FORMAT A35
       COL TBSP_NAME FORMAT A7
       COL STATUS FORMAT A7
       COL ERROR FORMAT A10
       COL CHANGE# FORMAT 99999999
       SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
              d.STATUS, r.ERROR, r.CHANGE#, r.TIME
       FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
       WHERE t.TS# = d.TS#
       AND d.FILE# = r.FILE#
       ;

   The `ERROR` column identifies the problem for each file requiring recovery.

See Also: Oracle Database Reference for information about the V$ views

### 6.3.2.3 Recovery of Read-Only Tablespaces

Recovery is not needed on any read-only tablespace during crash or instance recovery. During startup, recovery verifies that each online read-only datafile does not need media recovery, by checking whether the file was not restored from a backup taken before it was made read-only.

Note: If you restore a read-only tablespace from a backup taken before the tablespace was made read-only, then you cannot access the tablespace until you perform media recovery on it.

## 6.3.3 Determining your DBID

In situations requiring the recovery of your SPFILE or control file from autobackup, such as disaster recovery when you have lost all database files, you will need to use your DBID. Your DBID should be recorded along with other basic information about your database, as recommended in "Deciding Between ARCHIVELOG and NOARCHIVELOG Mode".

If you do not have a record of the DBID of your database, there are two places you can find it without opening your database.

- The DBID is used in forming the filename for the control file autobackup. Locate that file, and then refer to "Configuring the Control File Autobackup Format" to see where the DBID appears in the filename.

- If you have any text files that preserve the output from an RMAN session, the DBID is displayed by the RMAN client when it starts up and connects to your database. Typical output follows:

      % rman TARGET /
      Recovery Manager: Release 10.2.0.1.0 - Production on Sun Jun 12 02:41:03 2005

      Copyright (c) 1982, 2005, Oracle.  All rights reserved.

      connected to target database: RDBMS (DBID=774627068)

      RMAN>

## 6.3.4 Previewing Backups Used in Restore Operations: RESTORE PREVIEW

The `RESTORE` command supports a `PREVIEW` option, which identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry out a given restore operation, based on the information in the RMAN repository. Use `RESTORE`... `PREVIEW` when planning your restore and recovery operation, to ensure that all required backups are available or to identify situations in which you may want to direct RMAN to use or avoid specific backups.

For example, `RESTORE`... `PREVIEW` output may indicate that, during a `RESTORE` operation, RMAN will request a backup from a tape during the restore process which you know is temporarily unavailable. You can then use the `CHANGE`... `UNAVAILABLE` command (described in "Marking a Backup AVAILABLE or UNAVAILABLE") to set the backup status to UNAVAILABLE. If you then run `RESTORE`... `PREVIEW` again, RMAN will show you the backups it would use to perform a restore operation without using the unavailable backup.

In some cases, a backup may be listed as AVAILABLE but it is in fact vaulted, that is, stored remotely and requires retrieval before it can be used in a restore. If RMAN selects such a backup for use in a restore operation, the operation fails with an error. `RESTORE`... `PREVIEW` lets you identify any backups that are stored remotely, and `RESTORE`...`PREVIEW` `RECALL` is used to request that backups needed for a `RESTORE` operation but that are stored remotely be recalled from remote storage.

### 6.3.4.1 Using RESTORE... PREVIEW

`RESTORE` ... `PREVIEW` can be applied to any `RESTORE` operation to create a detailed report of every backup to be used in the requested `RESTORE` operation, as well as the necessary target SCN for recovery after the `RESTORE` operation is complete. Here are a few examples of `RESTORE` commands using the `PREVIEW` option:

    RESTORE DATABASE PREVIEW;
    RESTORE TABLESPACE users PREVIEW;
    RESTORE DATAFILE 3 PREVIEW;
    RESTORE ARCHIVELOG FROM LOGSEQ 200 PREVIEW;
    RESTORE ARCHIVELOG FROM TIME 'SYSDATE-7' PREVIEW;
    RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW;

`RESTORE`... `PREVIEW` output is in the same format as the output of the `LIST` command. See Oracle Database Backup and Recovery Reference for details on interpreting the output of `RESTORE`... `PREVIEW`.

### 6.3.4.2 Using RESTORE... PREVIEW SUMMARY

If the detailed report produced by `RESTORE`... `PREVIEW` provides more information than is needed, use the `RESTORE`... `PREVIEW` `SUMMARY` option to suppress much of the detail about specific files used and affected by the restore process. Here are some examples of `RESTORE` used with the `PREVIEW` `SUMMARY` option:

    RESTORE DATABASE PREVIEW SUMMARY;
    RESTORE TABLESPACE users PREVIEW SUMMARY;
    RESTORE DATAFILE 3 PREVIEW SUMMARY;
    RESTORE ARCHIVELOG FROM SCN 234546 PREVIEW SUMMARY;

`RESTORE`... `PREVIEW` `SUMMARY` reports are in the same format as the output from the `LIST SUMMARY` command. See Oracle Database Backup and Recovery Reference for details on interpreting the output of `RESTORE`... `PREVIEW SUMMARY`.

### 6.3.4.3 Using RESTORE... PREVIEW RECALL

`RESTORE`... `PREVIEW` `RECALL` can be used with any RESTORE operation, in cases a restore fails due to a needed backup being stored remotely.

The following command shows the output in a case where `RESTORE` ... `PREVIEW` indicates that a needed backup is stored remotely:

    RMAN> restore archivelog all preview;

    Starting restore at 10-JUN-05
    using channel ORA_DISK_1
    using channel ORA_SBT_TAPE_1

    List of Backup Sets
    ===================

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    31      12.75M     SBT_TAPE    00:00:02     10-JUN-05
            BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20050610T152755
            Handle: 15gmknbs   Media: /v1,15gmknbs

      List of Archived Logs in backup set 31
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    1       221154     06-JUN-05 222548     06-JUN-05
      1    2       222548     06-JUN-05 222554     06-JUN-05
      1    3       222554     06-JUN-05 222591     06-JUN-05
      1    4       222591     06-JUN-05 246629     07-JUN-05
      1    5       246629     07-JUN-05 262451     10-JUN-05

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    32      256.00K    SBT_TAPE    00:00:01     10-JUN-05
            BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20050610T153105
            Handle: 17gmknhp_1_1   Media: /v1,17gmknhp_1_1

      List of Archived Logs in backup set 32
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    6       262451     10-JUN-05 262547     10-JUN-05
      1    7       262547     10-JUN-05 262565     10-JUN-05

    List of remote backup files
    ============================
            Handle: 15gmknbs   Media: /v1,15gmknbs

The "List of remote backup files" at the end of the output identifies the backups that are stored remotely. In such a case, using `RESTORE` `ARCHIVELOG` `ALL` `PREVIEW` `RECALL` initiates recall for the remote backups and produces the following output:

    RMAN> restore archivelog all preview recall;

    Starting restore at 10-JUN-05
    using channel ORA_DISK_1
    using channel ORA_SBT_TAPE_1

    List of Backup Sets
    ===================

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    31      12.75M     SBT_TAPE    00:00:02     10-JUN-05
            BP Key: 33   Status: AVAILABLE  Compressed: NO  Tag: TAG20050610T152755
            Handle: 15gmknbs   Media: /v1,15gmknbs

      List of Archived Logs in backup set 31
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    1       221154     06-JUN-05 222548     06-JUN-05
      1    2       222548     06-JUN-05 222554     06-JUN-05
      1    3       222554     06-JUN-05 222591     06-JUN-05
      1    4       222591     06-JUN-05 246629     07-JUN-05
      1    5       246629     07-JUN-05 262451     10-JUN-05

    BS Key  Size       Device Type Elapsed Time Completion Time
    ------- ---------- ----------- ------------ ---------------
    32      256.00K    SBT_TAPE    00:00:01     10-JUN-05
            BP Key: 34   Status: AVAILABLE  Compressed: NO  Tag: TAG20050610T153105
            Handle: 17gmknhp_1_1   Media: /v1,17gmknhp_1_1

      List of Archived Logs in backup set 32
      Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
      ---- ------- ---------- --------- ---------- ---------
      1    6       262451     10-JUN-05 262547     10-JUN-05
      1    7       262547     10-JUN-05 262565     10-JUN-05

    Initiated recall for the following list of remote backup files
    ==========================================================
            Handle: 15gmknbs   Media: /v1,15gmknbs
    Finished restore at 10-JUN-05

You can repeat the `RESTORE`... `PREVIEW` command until no backups needed for the restore are reported as remote.

You can append `RECALL` to any `RESTORE`... `PREVIEW` command, as in these examples:

    RESTORE TABLESPACE users PREVIEW RECALL;
    RESTORE DATAFILE 3 PREVIEW RECALL;

## 6.3.5 Validating the Restore of Backups: RESTORE VALIDATE and VALIDATE BACKUPSET

The `RESTORE` ... `VALIDATE` and `VALIDATE` `BACKUPSET` commands test whether you can restore from your backups. You can test the availability of usable backups for any desired `RESTORE` operation, or test the contents of a specific backup for use in `RESTORE` operations. The contents of the backups are actually read and validated for corruption to ensure that the objects to be restored can be restored from them. You have these options:

- `RESTORE` `...` `VALIDATE` tests whether RMAN can restore a specific object from a backup. RMAN chooses which backups to use.

- `VALIDATE BACKUPSET` tests the validity of a backup set that you specify.

  See Also: Oracle Database Backup and Recovery Reference for `RESTORE` syntax Oracle Database Backup and Recovery Reference for `VALIDATE` syntax

### 6.3.5.1 Validating Restore from Backup with RESTORE ... VALIDATE

You can enter any valid `RESTORE` command specifying the `VALIDATE` clause to test whether usable backups for that `RESTORE` operation are available. When validating backups with `RESTORE... VALIDATE`, the database can be mounted or open.

This example illustrates validating the restore of the backup control file, `SYSTEM` tablespace, and all archived logs:

    RESTORE CONTROLFILE VALIDATE;
    RESTORE TABLESPACE SYSTEM VALIDATE;
    RESTORE ARCHIVELOG ALL VALIDATE;
    RESTORE DATAFILE 4,5,6 VALIDATE;

Note: You do not have to take datafiles offline when validating the restore of datafiles, because validation of backups of the datafiles only reads the backups and does not affect the production datafiles.

If you see error messages in the output and the following message, then RMAN cannot restore one or more of the specified files from your available backups:

    RMAN-06026: some targets not found - aborting restore

If you see an error message stack and output similar to the following, for example, then RMAN encountered a problem reading the specified backup:

    RMAN-03009: failure of restore command on c1 channel at 12-DEC-01 23:22:30
    ORA-19505: failed to identify file "oracle/dbs/1fafv9gl_1_1"
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3

If you do not see an error stack, then RMAN successfully tested restore of the specified objects from the available backups and should be able to restore these objects successfully during a real restore and recovery operation.

### 6.3.5.2 Validating Backup Sets with VALIDATE BACKUPSET

The `BACKUP` `VALIDATE` command requires that you know the primary keys of the backup sets that you want to validate.

To specify which backup sets to validate:

Find the backup sets that you want to validate by running `LIST` commands, noting primary keys. For example:

    LIST BACKUP;

Validate the restore of the backup sets, referencing them by the primary keys. This example validates the restore of backup sets 56 and 57:

    VALIDATE BACKUPSET 56,57;

If the output contains the message " `validation` `complete`", then RMAN successfully validated the restore of the specified backup set. For example:

    using channel ORA_DISK_1
    channel ORA_DISK_1: starting validation of archive log backupset
    channel ORA_DISK_1: restored backup piece 1
    piece handle=/oracle/dbs/0mdg9v8l_1_1 tag=TAG20020208T155604 params=NULL
    channel ORA_DISK_1: validation complete